/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.tspp.dao;

import com.tspp.common.ResourceManager;
import com.tspp.dao.dto.Entrant;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;


/**
 * Concrete realization of entrantDao interface. Retrieve the connection from pool in every method
 * @author Maks
 */
public class EntrantDaoImpl extends AbstractDao<Entrant> implements EntrantDao {

    /**  All finder methods in this class use this SELECT constant to build their queries*/
    private static final String SQL_SELECT = "SELECT ID_ENTRANT, ID_GROUP, NAME, SURNAME, ADDRESS FROM " + getTableName() + "";
    /** SQL INSERT statement for this table */
    private static final String SQL_INSERT = "INSERT INTO " + getTableName() + " (ID_GROUP, NAME, SURNAME, ADDRESS ) VALUES ( ?, ?, ?, ? )";
    /**SQL UPDATE statement for this table*/
    private static final String SQL_UPDATE = "UPDATE " + getTableName() + " SET ID_GROUP = ?, NAME = ?, SURNAME = ?, ADDRESS = ? WHERE ID_ENTRANT = ?";
    /** SQL DELETE statement for this table*/
    private static final String SQL_DELETE = "DELETE FROM " + getTableName() + " WHERE ID_ENTRANT = ?";
    /** Index of column ID_ENTRANT*/
    private static final int COLUMN_ID_ENTRANT = 1;
    /**Index of column ID_GROUP*/
    private static final int COLUMN_ID_GROUP = 2;
    /** Index of column NAME*/
    private static final int COLUMN_NAME = 3;
    /** Index of column SURNAME*/
    private static final int COLUMN_SURNAME = 4;
    /** Index of column ADDRESS*/
    private static final int COLUMN_ADDRESS = 5;

    /**
     * Inserts a new row in the ENTRANT table.
     */
    public void insert(Entrant dto) throws DaoException {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            //get a connection from the ResourceManager
            conn = ResourceManager.getConnection();

            stmt = conn.prepareStatement(SQL_INSERT);
            int index = 1;
            if (dto.getIdGroup() == -1) {
                stmt.setNull(index++, java.sql.Types.INTEGER);
            } else {
                stmt.setInt(index++, dto.getIdGroup());
            }
            stmt.setString(index++, dto.getName());
            stmt.setString(index++, dto.getSurname());
            stmt.setString(index++, dto.getAddress());
            //System.out.println("Executing " + SQL_INSERT + " with DTO: " + dto);
            stmt.executeUpdate();
        } catch (Exception _e) {
            throw new DaoException("Exception: " + _e.getMessage(), _e);
        } finally {
            ResourceManager.close(stmt);
            ResourceManager.close(conn);
        }

    }

    /**
     * Updates a single row in the ENTRANT table.
     */
    public void update(int entrant, Entrant dto) throws DaoException {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            // get a connection from the ResourceManager
            conn = ResourceManager.getConnection();
            //System.out.println("Executing " + SQL_UPDATE + " with DTO: " + dto);
            stmt = conn.prepareStatement(SQL_UPDATE);
            int index = 1;
            if (dto.getIdGroup() == -1) {
                stmt.setNull(index++, java.sql.Types.INTEGER);
            } else {
                stmt.setInt(index++, dto.getIdGroup());
            }
            stmt.setString(index++, dto.getName());
            stmt.setString(index++, dto.getSurname());
            stmt.setString(index++, dto.getAddress());
            stmt.setInt(index++, entrant);
            stmt.executeUpdate();
        } catch (Exception _e) {
            throw new DaoException("Exception: " + _e.getMessage(), _e);
        } finally {
            ResourceManager.close(stmt);
            ResourceManager.close(conn);
        }
    }

    /**
     * Deletes a single row in the ENTRANT table.
     */
    public void delete(int entrant) throws DaoException {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ResourceManager.getConnection();
            //System.out.println("Executing " + SQL_DELETE + " with PK: " + pk);
            stmt = conn.prepareStatement(SQL_DELETE);
            stmt.setInt(1, entrant);
            stmt.executeUpdate();
        } catch (Exception _e) {
            throw new DaoException("Exception: " + _e.getMessage(), _e);
        } finally {
            ResourceManager.close(stmt);
            ResourceManager.close(conn);
        }

    }

    /**
     * Returns all rows from the ENTRANT table that match the criteria 'ID_ENTRANT = :idEntrant'.
     */
    public Entrant findByPrimaryKey(int idEntrant) throws DaoException {
        List<Entrant> ret = findByDynamicSelect(SQL_SELECT + " WHERE ID_ENTRANT = ?", new Object[]{new Integer(idEntrant)});
        return ret.isEmpty() ? null : ret.get(0);
    }

    /**
     * Returns all rows from the ENTRANT table that match the criteria ''.
     */
    public List<Entrant> findAll() throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " ORDER BY ID_ENTRANT", null);
    }

    /**
     * Returns all rows from the ENTRANT table that match the criteria 'ID_GROUP = :idGroup'.
     */
    public List<Entrant> findByGroupa(int idGroup) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE ID_GROUP = ?", new Object[]{new Integer(idGroup)});
    }

    /**
     * Returns all rows from the ENTRANT table that match the criteria 'ID_GROUP = :idGroup'.
     */
    public List<Entrant> findWhereIdGroupEquals(int idGroup) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE ID_GROUP = ? ORDER BY ID_GROUP", new Object[]{new Integer(idGroup)});
    }

    /**
     * Returns all rows from the ENTRANT table that match the criteria 'NAME = :name'.
     */
    public List<Entrant> findWhereNameEquals(String name) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE NAME = ? ORDER BY NAME", new Object[]{name});
    }

    /**
     * Returns all rows from the ENTRANT table that match the criteria 'SURNAME = :surname'.
     */
    public List<Entrant> findWhereSurnameEquals(String surname) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE SURNAME = ? ORDER BY SURNAME", new Object[]{surname});
    }

    /**
     * Returns all rows from the ENTRANT table that match the criteria 'ADDRESS = :address'.
     */
    public List<Entrant> findWhereAddressEquals(String address) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE ADDRESS = ? ORDER BY ADDRESS", new Object[]{address});
    }

    /**
     * Return table name this dao is operated with
     * @return String
     */
    public static String getTableName() {
        return "ENTRANT";
    }

    /**
     * Implements abstract getDtoFrom of AbstractDao
     * @see AbstractDAO
     * @param rs
     * @return newly created transfer object
     * @throws SQLException
     */
    @Override
    protected Entrant getDtoFrom(ResultSet rs) throws SQLException {
        Entrant dto = new Entrant();
        dto.setIdEntrant(rs.getInt(COLUMN_ID_ENTRANT));
        dto.setIdGroup(rs.getInt(COLUMN_ID_GROUP));
        if (rs.wasNull()) {
            dto.setIdGroup(-1);
        }
        dto.setName(rs.getString(COLUMN_NAME));
        dto.setSurname(rs.getString(COLUMN_SURNAME));
        dto.setAddress(rs.getString(COLUMN_ADDRESS));
        return dto;
    }
}